﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Security.Cryptography;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace BestBuy.DAL
{

    public class CNhanVien
    {
        private SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["TradeKeyConnectionString"].ConnectionString);
        private TradeKeyDataContext content = new TradeKeyDataContext();
        public CNhanVien() { }

        public string MaHoaMatKhau(string matkhau)
        {
            string str = "";
            Byte[] mangpam = System.Text.Encoding.UTF8.GetBytes(matkhau);
            MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();
            mangpam = md5.ComputeHash(mangpam);
            foreach (Byte b in mangpam)
            {
                str += b.ToString("x2");
            }
            return str;
        }

        public NhanVien CheckLogin(string email, string matkhau)
        {
            string mahoa = this.MaHoaMatKhau(matkhau);
            var q = (from nv in content.NhanViens
                    where nv.TinhTrang == true && nv.Email == email && nv.MatKhau == mahoa
                    select nv).FirstOrDefault();
            return q;
        }

        public NhanVien GetAccountID(int idtaikhoan)
        {
            var q = (from sp in content.NhanViens
                     where sp.IDTaiKhoan == idtaikhoan
                     select sp).FirstOrDefault();
            return q;
        }

        public bool CheckPassword(int idtaikhoan, string matkhau)
        {
            string mk = this.GetAccountID(idtaikhoan).MatKhau.ToString();
            string mahoamk = this.MaHoaMatKhau(matkhau);
            if (mk.Equals(mahoamk))
            {
                return true;
            }
            return false;
        }

        public bool UpdatePassword(int idtaikhoan, string matkhau)
        {
            string mahoamk = this.MaHoaMatKhau(matkhau);
            NhanVien tk = this.GetAccountID(idtaikhoan);
            tk.MatKhau = mahoamk;
            try
            {
                content.SubmitChanges();
            }
            catch
            {
                return false;
            }
            return true;
        }

        public bool LockAccount(int id)
        {
            NhanVien tk = this.GetAccountID(id);
            tk.TinhTrang = false;
            try
            {
                content.SubmitChanges();
            }
            catch
            {
                return false;
            }
            return true;
        }

        public int soLuongDuLieu(string strQuery)
        {
            sqlCon.Open();
            string str = "select COUNT(*) from NhanVien dl where dl.TinhTrang='True'" + strQuery;
            SqlCommand cmd = new SqlCommand(str, sqlCon);
            int soLuong = Convert.ToInt32(cmd.ExecuteScalar());
            sqlCon.Close();
            return soLuong;
        }
        public int slTrangDuLieu(int soLuongTrenTrang, string strQuery)
        {
            int soLuong = this.soLuongDuLieu(strQuery);
            if (soLuong > 0)
            {
                int soTrang = soLuong / soLuongTrenTrang;
                if (soLuong % soLuongTrenTrang != 0)
                {
                    soTrang++;
                }

                return soTrang;
            }
            return -1;
        }

        public int[] FilterMaDuLieu(string strWhere, int startPoint, int size)
        {
            DataTable dt = new DataTable();
            int[] arrMaDuLieu = new int[size];

            string sql = "select dl.IDTaiKhoan,ROW_NUMBER() over (order by dl.IDTaiKhoan desc) as row_num from NhanVien dl where  dl.TinhTrang='True'" + strWhere;
            string sql1 = "select distinct(t0.IDTaiKhoan) from (" + sql + ") as t0 where t0.row_num between " + (startPoint + 1) + " and " + (startPoint + size);
            sqlCon.Open();
            SqlCommand cmd = new SqlCommand(sql1, sqlCon);
            SqlDataReader dr = cmd.ExecuteReader();
            int i = 0;
            while (dr.Read())
            {
                arrMaDuLieu[i] = int.Parse(dr["IDTaiKhoan"].ToString());
                i++;
            }

            sqlCon.Close();

            return arrMaDuLieu;
        }

        public List<NhanVien> GetList(string strQuery, int begin, int size)
        {
            int[] arrMaDuLieu = this.FilterMaDuLieu(strQuery, begin, size);

            var result = (from dl in content.NhanViens
                          where arrMaDuLieu.Contains(dl.IDTaiKhoan)
                          orderby dl.IDTaiKhoan descending
                          select dl).ToList();
            return result;
        }

        public bool InsertInfoStaff(NhanVien tt)
        {
            try
            {
                content.NhanViens.InsertOnSubmit(tt);
                content.SubmitChanges();
            }
            catch
            {
                return false;
            }
            return true;
        }

        public bool EmailExistTest(string email)
        {
            var q = (from sp in content.NhanViens
                     where sp.Email.ToUpper() == email.ToUpper()
                     select sp).FirstOrDefault();
            if (q == null)
                return true;
            return false;
        }

        public bool EmailExistTestID(string email,int idtaikhoan)
        {
            var q = (from sp in content.NhanViens
                     where sp.Email.ToUpper() == email.ToUpper() && sp.IDTaiKhoan != idtaikhoan
                     select sp).FirstOrDefault();
            if (q == null)
                return true;
            return false;
        }

        public bool UpdateStaff(NhanVien nv)
        {
            var q = (from sp in content.NhanViens
                     where sp.IDTaiKhoan == nv.IDTaiKhoan
                     select sp).FirstOrDefault();
            q.GioiTinh = nv.GioiTinh;
            q.DiaChi = nv.DiaChi;
            q.Email = nv.Email;
            q.HoTen = nv.HoTen;
            try 
            {
                content.SubmitChanges();
            }
            catch
            {
                return false;
            }
            return true;
        }
    }
}